﻿Imports DTOLayer
Imports System.Data.SqlClient
Public Class PhuTungDao
    Public Function LayDanhSach() As List(Of PhuTungDto)
        Dim ds As New List(Of PhuTungDto)
        Dim con As SqlConnection
        Dim strSQL As String
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        con = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        strSQL = "Select * From PhuTung"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, con)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        Dim phuTung As New PhuTungDto()
        While (dr.Read())
            phuTung = New PhuTungDto()
            phuTung.idPhuTung = dr("idPhuTung")
            phuTung.TenPhuTung = dr("TenPhuTung")
            phuTung.NuocSanXuat = dr("NuocSanXuat")
            phuTung.Gia = dr("Gia")
            ds.Add(phuTung)
        End While
        'B5: Dong ket noi CSDL
        con.Close()
        Return ds
    End Function

    Public Function TraCuu(ByVal idPhuTung As Integer) As PhuTungDto
        Dim phuTung As New PhuTungDto
        Dim cn As SqlConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From PhuTung Where idPhuTung = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@idPhuTung", SqlDbType.VarChar)
        cmd.Parameters("@idPhuTung").Value = idPhuTung
        'B4: Thuc thi chuoi strSQL
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        While dr.Read()            
            phuTung.idPhuTung = idPhuTung
            phuTung.TenPhuTung = dr("TenPhuTung")
            phuTung.NuocSanXuat = dr("NuocSanXuat")
            phuTung.Gia = dr("Gia")            
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return phuTung
    End Function

    Public Sub Them(ByVal phuTungDto As PhuTungDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into PhuTung(TenPhuTung,NuocSanXuat,Gia) values(?,?,?)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@TenPhuTung", SqlDbType.NVarChar)
        cmd.Parameters("@TenPhuTung").Value = phuTungDto.TenPhuTung
        cmd.Parameters.Add("@NuocSanXuat", SqlDbType.NVarChar)
        cmd.Parameters("@NuocSanXuat").Value = phuTungDto.NuocSanXuat
        cmd.Parameters.Add("@Gia", SqlDbType.VarChar)
        cmd.Parameters("@Gia").Value = phuTungDto.Gia        
        cmd.ExecuteNonQuery()
        strSQL = "Select @@IDENTITY"
        cmd = New SqlCommand(strSQL, cn)
        phuTungDto.idPhuTung = cmd.ExecuteScalar()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Sua(ByVal phuTungDto As PhuTungDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Update PhuTung set TenPhuTung=?, NuocSanXuat=?, Gia=? where idPhuTung=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@TenPhuTung", SqlDbType.NVarChar)
        cmd.Parameters("@TenPhuTung").Value = phuTungDto.TenPhuTung
        cmd.Parameters.Add("@NuocSanXuat", SqlDbType.NVarChar)
        cmd.Parameters("@NuocSanXuat").Value = phuTungDto.NuocSanXuat
        cmd.Parameters.Add("@Gia", SqlDbType.Float)
        cmd.Parameters("@Gia").Value = phuTungDto.Gia
        cmd.Parameters.Add("@idPhuTung", SqlDbType.Int)
        cmd.Parameters("@idPhuTung").Value = phuTungDto.idPhuTung        
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Xoa(ByVal idPhuTung As Integer)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "delete PhuTung where idPhuTung=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@idPhuTung", SqlDbType.Int)
        cmd.Parameters("@idPhuTung").Value = idPhuTung
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub CapNhatBang(ByVal dt As DataTable)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From PhuTung"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        Dim cb As New SqlCommandBuilder(da)
        da.Update(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Function LayBang() As DataTable
        Dim dt As New DataTable()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From PhuTung"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function
End Class
